﻿Imports System.Data.Linq
Imports System.Linq

Public Class MFiles
    Private db As New TanNamChinhDataContext
 
    Public Sub InsertNewRecord(ByVal obj As tblFile)
        'db.tblCurrencies.InsertOnSubmit(obj)
        db.GetTable(Of tblFile)().InsertOnSubmit(obj)
        db.SubmitChanges()
    End Sub

    Public Sub UpdateRecord(ByVal obj As tblFile)
        Dim getData = (From b In db.tblFiles
                                Where b.ID_File = obj.ID_File
                                Select b).Single()
        getData.CreatedDate = obj.CreatedDate
        getData.ID_Customer = obj.ID_Customer
        getData.ID_Company = obj.ID_Company
        getData.ID_ForeignCompany = obj.ID_ForeignCompany
        getData.ID_Agent = obj.ID_Agent 
        getData.IsExport = obj.IsExport
        getData.IsD2D = obj.IsD2D
        getData.IsFCL = obj.IsFCL
        getData.ID_Unit = obj.ID_Unit

        db.SubmitChanges()

    End Sub
    Public Sub UpdateRatioBuying(ByVal obj As tblFile)
        Dim getData = (From b In db.tblFiles
                                Where b.ID_File = obj.ID_File
                                Select b).Single()
      
        getData.Ratio = obj.Ratio

        db.SubmitChanges()

    End Sub
    Public Sub DeleteRecord(ByVal id As Integer)
        Dim getData = (From b In db.tblFiles
                                Where b.ID_File = id
                                Select b).Single()
        db.tblFiles.DeleteOnSubmit(getData)
        db.SubmitChanges()
    End Sub
    Public Function getIDNewFile() As Integer
        Dim id_f As Integer
        Try
            id_f = (From b In db.tblFiles
                     Order By b.ID_File Descending
                        Select b.ID_File
                       ).First()
        Catch ex As Exception
            Throw ex
        End Try
        Return id_f
    End Function
    Public Function getFileNoFile(ByVal idfile As Integer) As String
        Dim fileNos As String
        Try
            fileNos = (From b In db.tblFiles
                      Where b.ID_File = idfile
                      Order By b.FileNo
                      Select b.FileNo).First
        Catch ex As Exception
            ' Throw ex
            Return fileNos = 0
        End Try
        Return fileNos
    End Function
    Public Function getFileType(ByVal idfile As Integer) As Boolean
        Dim isex As Boolean
        Try
            isex = (From b In db.tblFiles
                      Where b.ID_File = idfile
                      Order By b.FileNo
                      Select b.IsExport).First
        Catch ex As Exception
            ' Throw ex
            Return isex = True
        End Try
        Return isex
    End Function

    Public Function getIDFile(ByVal FileNo As String, ByVal isExport As Boolean) As Integer
        Dim idFile As Integer
        Try
            idFile = (From b In db.tblFiles
                      Where b.FileNo = FileNo And b.IsExport = isExport
                      Order By b.ID_File
                      Select b.ID_File).First
        Catch ex As Exception
            ' Throw ex
            Return idFile = 0
        End Try
        Return idFile
    End Function

    Public Function getFileNoFile2(ByVal idfile As Integer) As String
        Dim fileNos As String
        Try
            fileNos = (From b In db.tblFiles
                      Where b.ID_File = idfile
                      Order By b.FileNo
                      Select b.FileNo).First
        Catch ex As Exception
            ' Throw ex
            Return fileNos = 0
        End Try
        Return fileNos
    End Function
    Public Function GetRatioCurrency(ByVal idFile As Integer) As Double
        Dim ratios As Double
        Try
            ratios = (From b In db.tblFiles
                     Where b.ID_File = idFile
                     Select b.Ratio).First()
        Catch ex As Exception
            Return 0
        End Try
        Return ratios
    End Function
    Public Function GetRatioCurrencyFollowHBL(ByVal idFile As Integer, ByVal idHBL As Integer, ByVal idCurrency As Integer) As Double
        Dim ratios As Double
        Try
            ratios = (From b In db.tblHBLExchanges
                     Where b.ID_HBL = idHBL And b.ID_Currency = idCurrency
                     Select b.Ratio).First()
        Catch ex As Exception
            Return 0
        End Try
        Return ratios
    End Function
    Public Function getCustomerOfFile(ByVal idFile As Integer) As Integer
        Dim idCus As Integer
        Try
            idCus = (From b In db.tblFiles
                     Where b.ID_File = idFile
                     Select b.ID_Customer).First()
        Catch ex As Exception
            Return 0
        End Try
        Return idCus
    End Function
    Public Function getTelCustomerOfFile(ByVal idFile As Integer) As String
        Dim idCus As Integer = getCustomerOfFile(idFile)
        Dim tels As String = ""
        Try
            tels = (From b In db.tblCustomers
                     Where b.ID_Customer = idCus
                     Select b.Tel).First()
        Catch ex As Exception
            Return ""
        End Try
        Return tels
    End Function
    Public Function getETDOfFile(ByVal idHBL As Integer) As DateTime
        Dim etds As DateTime
        Try
            etds = (From b In db.tblHBLs
                     Where b.ID_HBL = idHBL
                     Select b.tblFileMBL.tblMBL.tblVoyage.ETD).First()
        Catch ex As Exception

        End Try
        Return etds
    End Function
    Public Function getPODOfFile(ByVal idHBL As Integer) As String
        Dim name As String = ""
        Try
            name = (From b In db.tblHBLs
                        Where b.ID_HBL = idHBL
                     Select b.tblFileMBL.tblMBL.tblVoyage.tblPort1.Port).First()
        Catch ex As Exception

        End Try
        Return name
    End Function
    Public Function getBLNameOfFile(ByVal idHBL As Integer) As String
        Dim name As String = ""
        Try
            name = (From b In db.tblHBLs
                        Where b.ID_HBL = idHBL
                     Select b.HBLNo).First
        Catch ex As Exception

        End Try
        Return name
    End Function
    Public Function GetBillOfLadingOfFile(ByVal idFile As Integer) As String

        Dim isMasters  
        Try
            isMasters = (From b In db.tblHBLs
                     Where b.tblFileMBL.ID_File = idFile
                     Select b.IsMaster).First()
        Catch ex As Exception
            Throw ex
        End Try
        Dim bill
        Dim bills As String = ""
        Try
            If isMasters = True Then
                'Nếu là master thì lấy các giá trị MBL
                bill = From b In db.tblFileMBLs
                       Where b.ID_File = idFile
                       Select b.tblMBL.MBLNo
                For Each item As String In bill
                    bills &= item + ","
                Next
            Else
                'Không phải là master thì lấy giá trị hbl
                bill = From b In db.tblHBLs
                       Where b.tblFileMBL.ID_File = idFile
                       Select b.tblFileMBL.tblMBL.MBLNo
                For Each item As String In bill
                    bills &= item + ","
                Next
            End If
        Catch ex As Exception

        End Try
        Return bills
    End Function
    ''' <summary>
    ''' Lấy danh sách các phí của file lưu trong file fee
    ''' </summary>
    ''' <param name="idFile"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Sub getFeeOfFile(ByVal idFile As Integer)
        Dim feeList
        Try
            feeList = From b In db.tblFileFees
                     Where b.ID_File = idFile
                     Select b.ID_FileFee, b.ID_Fee, b.tblFee.FeeName, b.Price

        Catch ex As Exception

        End Try

    End Sub
    Public Function getSellingFeeOfFile(ByVal idFile As Integer) As IQueryable
        Dim query = Nothing
        Try
            query = From b In db.tblSellingFees
                   Where b.tblHBL.tblFileMBL.ID_File = idFile And b.tblFee.ID_FeeType = 2
                   Select b
        Catch ex As Exception

        End Try
        Return query
    End Function
End Class
